package com.dao.imp;

import com.bean.EDelivery;
import com.bean.Express;
import com.dao.BaseEDeliveryDao;
import com.exception.idNumberException;
import com.exception.phoneException;
import com.util.DruidUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author LEIKE
 * @Title : EDeliveryDaoMysql
 * @Description: DAO连接MySQL处理快递员相关业务
 * @Date_Created_in: 2021/5/26 19:00
 * @Modified_By:
 * @Project: com.dao.imp
 */
public class EDeliveryDaoMysql extends DruidUtil implements BaseEDeliveryDao {

    // 查 统计 快递员
    public static final String SQL_CONSOLE = "SELECT COUNT(id) date1_Size , " +
            "COUNT(TO_DAYS(registeredtime)=TO_DAYS(NOW()) OR NULL) data1_Day " +
            "FROM edelivery";
    // 查 总数 快递员信息
    public static final String SQL_FIND_ALL = "SELECT * FROM edelivery";
    // 查 分页 快递员信息
    public static final String SQL_FIND_LIMIT = "SELECT * FROM edelivery LIMIT ? , ? ";
    // 查 指定 快递员信息
    public static final String SQL_FIND_BY_PHONE = "SELECT * FROM edelivery WHERE phone = ?";
    // 录入 快递员
    public static final String SQL_INSERT =
            "INSERT INTO edelivery (name,phone,idnumber,password,registeredtime,status) VALUES (?,?,?,?,NOW(),0)";
    // 修改 快递员
    public static final String SQL_UPDATE = "UPDATE edelivery SET name=?,phone=?,idnumber=?,password=? WHERE phone=? ";
    // 删除 快递员
    public static final String SQL_DELETE = "DELETE FROM edelivery WHERE phone=?";
    @Override
    public Map<String, Integer> console() {
        Map<String,Integer> data = new HashMap<>();
        ResultSet result = query(SQL_CONSOLE , null);
        try {
            while (result.next()){
                int data1_Size = result.getInt("date1_Size");
                int data1_Day = result.getInt("data1_Day");

                data.put("data1_Size",data1_Size);
                data.put("data1_Day",data1_Day);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (result != null) {
                    result.close();
                }
                closeDaoAll();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return data;
    }

    @Override
    public List<EDelivery> findAll(boolean limit, int offset, int pageNumber) {
        List<EDelivery> eDList = new ArrayList<>();
        ResultSet result = null;
        try {
            if (limit){
                List params = new ArrayList();
                params.add(offset);
                params.add(pageNumber);
                result = query(SQL_FIND_LIMIT, params);
            }else{
                result = query(SQL_FIND_ALL, null);
            }
            while(result.next()){
                int id = result.getInt("id");
                String name = result.getString("name");
                String phone = result.getString("phone");
                String idnumber = result.getString("idnumber");
                String password = result.getString("password");
                String deliveryNumber = result.getString("deliverynumber");
                Timestamp registeredTime = result.getTimestamp("registeredtime");
                Timestamp lastTime = result.getTimestamp("lasttime");
                int status = result.getInt("status");
                EDelivery newE = new EDelivery(id, name, phone, idnumber, password, deliveryNumber, registeredTime, lastTime, status);
                eDList.add(newE);
            }
//            eDList = refexClass(EDelivery.class);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (result != null) {
                    result.close();
                }
                closeDaoAll();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return eDList;
    }

    /**
     * 通过手机号码查找快递员信息，是update和 delete的先决步骤
     * @param phoneNumber 查找快递员的手机号
     * @return 快递员列表
     */
    @Override
    public List<EDelivery> findByPhoneNumber(String phoneNumber) {
        List<EDelivery> eDList = null;
        // System.out.println("Dao findByPhone: " + phoneNumber);
        List params = new ArrayList();
        params.add(phoneNumber);
        query(SQL_FIND_BY_PHONE,params);
        try {
            eDList = refexClass(EDelivery.class);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return eDList;
    }

    @Override
    public boolean insert(EDelivery newE) throws idNumberException, phoneException {
        List params = new ArrayList();
        params.add(newE.getName());
        params.add(newE.getPhone());
        params.add(newE.getIdNumber());
        params.add(newE.getPassWord());
        boolean find = false;
        try {
            find = update(SQL_INSERT,params)>0;
        } catch (SQLException e1) {
            String info = e1.getMessage();
            if(info.endsWith("for key 'edelivery.idnumber'")){
                idNumberException e2 = new idNumberException(info);
                throw e2;
            }else if(info.endsWith("for key 'edelivery.phone'")){
                phoneException e2 = new phoneException(info);
                throw e2;
            }
//            System.out.println(info);
        }finally {
            closeDaoAll();
        }
        return find;
    }

    /**
     * @param phone
     * @param newE
     * @return
     */
    @Override
    public boolean update(String phone, EDelivery newE) {
        List params = new ArrayList();
        params.add(newE.getName());
        params.add(newE.getPhone());
        params.add(newE.getIdNumber());
        params.add(newE.getPassWord());
        params.add(phone);
        boolean find = false;
        try {
            find = update(SQL_UPDATE,params)>0;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return find;
    }

    /**
     * DELETE FROM edelivery WHERE phone=?
     * @param phone
     * @return
     */
    @Override
    public boolean delete(String phone) {
        List params = new ArrayList();
        params.add(phone);
        // System.out.println("Dao delete: " + phone);
        boolean find = false;
        try {
            find = update(SQL_DELETE,params)>0;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return find;
    }
}
